// models/SysUserRole.js
const mongoose = require("mongoose");
const { Schema } = mongoose;

/**
 * 用户和角色关联表 - 对应 MySQL sys_user_role 表
 * 多对多关系，使用复合主键
 */
const sysUserRoleSchema = new Schema(
  {
    // 用户ID - 对应MySQL的user_id
    userId: {
      type: Number,
      required: [true, "用户ID不能为空"],
      index: true,
      comment: "用户ID",
    },

    // 角色ID - 对应MySQL的role_id
    roleId: {
      type: Number,
      required: [true, "角色ID不能为空"],
      index: true,
      comment: "角色ID",
    },

    // 创建时间 - MongoDB扩展字段，便于查询和管理
    createTime: {
      type: Date,
      default: Date.now,
      comment: "创建时间",
    },

    // 创建者 - MongoDB扩展字段
    createBy: {
      type: String,
      default: "",
      comment: "创建者",
    },
  },
  {
    // Schema 选项
    timestamps: false,
    collection: "sys_user_roles",
  }
);

// 创建复合唯一索引，模拟MySQL的复合主键
sysUserRoleSchema.index({ userId: 1, roleId: 1 }, { unique: true });

// 创建反向索引，便于双向查询
sysUserRoleSchema.index({ roleId: 1, userId: 1 });

// 虚拟字段 - 用户信息（需要populate）
sysUserRoleSchema.virtual("user", {
  ref: "SysUser",
  localField: "userId",
  foreignField: "userId",
  justOne: true,
});

// 虚拟字段 - 角色信息（需要populate）
sysUserRoleSchema.virtual("role", {
  ref: "SysRole",
  localField: "roleId",
  foreignField: "roleId",
  justOne: true,
});

// 实例方法
sysUserRoleSchema.methods = {
  // 获取关联信息（包含用户和角色详情）
  toDetailJSON: async function () {
    await this.populate("user", "userName nickName email status");
    await this.populate("role", "roleName roleKey status");

    return {
      userId: this.userId,
      roleId: this.roleId,
      user: this.user
        ? {
            userName: this.user.userName,
            nickName: this.user.nickName,
            email: this.user.email,
            status: this.user.status,
          }
        : null,
      role: this.role
        ? {
            roleName: this.role.roleName,
            roleKey: this.role.roleKey,
            status: this.role.status,
          }
        : null,
      createTime: this.createTime,
      createBy: this.createBy,
    };
  },
};

// 静态方法
sysUserRoleSchema.statics = {
  /**
   * 为用户分配角色
   */
  assignRolesToUser: function (userId, roleIds, createBy = "") {
    // 先删除用户现有的所有角色
    return this.deleteUserRoles(userId).then(() => {
      // 批量插入新的角色关联
      const userRoles = roleIds.map((roleId) => ({
        userId: userId,
        roleId: roleId,
        createBy: createBy,
        createTime: new Date(),
      }));

      return this.insertMany(userRoles);
    });
  },

  /**
   * 为角色分配用户
   */
  assignUsersToRole: function (roleId, userIds, createBy = "") {
    // 先删除角色的所有现有用户
    return this.deleteRoleUsers(roleId).then(() => {
      // 批量插入新的用户关联
      const userRoles = userIds.map((userId) => ({
        userId: userId,
        roleId: roleId,
        createBy: createBy,
        createTime: new Date(),
      }));

      return this.insertMany(userRoles);
    });
  },

  /**
   * 删除用户的所有角色
   */
  deleteUserRoles: function (userId) {
    return this.deleteMany({ userId: userId });
  },

  /**
   * 删除角色的所有用户
   */
  deleteRoleUsers: function (roleId) {
    return this.deleteMany({ roleId: roleId });
  },

  /**
   * 删除特定的用户角色关联
   */
  deleteUserRole: function (userId, roleId) {
    return this.deleteOne({ userId: userId, roleId: roleId });
  },

  /**
   * 检查用户是否拥有某个角色
   */
  hasUserRole: function (userId, roleId) {
    return this.findOne({ userId: userId, roleId: roleId });
  },

  /**
   * 检查用户是否拥有某个角色（通过角色键名）
   */
  hasUserRoleByKey: async function (userId, roleKey) {
    const SysRole = mongoose.model("SysRole");
    const role = await SysRole.findOne({ roleKey: roleKey });

    if (!role) {
      return false;
    }

    return this.findOne({ userId: userId, roleId: role.roleId });
  },

  /**
   * 获取用户的所有角色ID
   */
  getUserRoleIds: function (userId) {
    return this.find({ userId: userId })
      .select("roleId -_id")
      .then((roles) => roles.map((role) => role.roleId));
  },

  /**
   * 获取用户的所有角色信息
   */
  getUserRoles: function (userId) {
    return this.find({ userId: userId })
      .populate("role", "roleId roleName roleKey dataScope status")
      .then((roles) => roles.map((role) => role.role));
  },

  /**
   * 获取角色的所有用户ID
   */
  getRoleUserIds: function (roleId) {
    return this.find({ roleId: roleId })
      .select("userId -_id")
      .then((users) => users.map((user) => user.userId));
  },

  /**
   * 获取角色的所有用户信息
   */
  getRoleUsers: function (roleId) {
    return this.find({ roleId: roleId })
      .populate("user", "userId userName nickName email status")
      .then((users) => users.map((user) => user.user));
  },

  /**
   * 批量获取用户的角色信息
   */
  getUsersRoles: function (userIds) {
    return this.find({ userId: { $in: userIds } })
      .populate("role", "roleId roleName roleKey status")
      .then((relations) => {
        const result = {};
        relations.forEach((relation) => {
          if (!result[relation.userId]) {
            result[relation.userId] = [];
          }
          result[relation.userId].push(relation.role);
        });
        return result;
      });
  },

  /**
   * 批量获取角色的用户信息
   */
  getRolesUsers: function (roleIds) {
    return this.find({ roleId: { $in: roleIds } })
      .populate("user", "userId userName nickName email status")
      .then((relations) => {
        const result = {};
        relations.forEach((relation) => {
          if (!result[relation.roleId]) {
            result[relation.roleId] = [];
          }
          result[relation.roleId].push(relation.user);
        });
        return result;
      });
  },

  /**
   * 分页查询用户角色关联
   */
  findUserRolesPaginated: function (options = {}) {
    const { page = 1, limit = 10, userId = null, roleId = null } = options;

    const skip = (page - 1) * limit;
    const query = {};

    if (userId) {
      query.userId = userId;
    }

    if (roleId) {
      query.roleId = roleId;
    }

    return Promise.all([
      this.find(query)
        .populate("user", "userName nickName email status")
        .populate("role", "roleName roleKey status")
        .skip(skip)
        .limit(limit)
        .sort({ createTime: -1 }),
      this.countDocuments(query),
    ]).then(([userRoles, total]) => ({
      userRoles,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        pages: Math.ceil(total / limit),
      },
    }));
  },

  /**
   * 统计用户角色数量
   */
  getUserRoleStats: function () {
    return this.aggregate([
      {
        $group: {
          _id: "$userId",
          roleCount: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: null,
          totalUsers: { $sum: 1 },
          avgRolesPerUser: { $avg: "$roleCount" },
          maxRolesPerUser: { $max: "$roleCount" },
          minRolesPerUser: { $min: "$roleCount" },
        },
      },
      {
        $project: {
          _id: 0,
          totalUsers: 1,
          avgRolesPerUser: { $round: ["$avgRolesPerUser", 2] },
          maxRolesPerUser: 1,
          minRolesPerUser: 1,
        },
      },
    ]);
  },

  /**
   * 统计角色用户数量
   */
  getRoleUserStats: function () {
    return this.aggregate([
      {
        $group: {
          _id: "$roleId",
          userCount: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: null,
          totalRoles: { $sum: 1 },
          avgUsersPerRole: { $avg: "$userCount" },
          maxUsersPerRole: { $max: "$userCount" },
          minUsersPerRole: { $min: "$userCount" },
        },
      },
      {
        $project: {
          _id: 0,
          totalRoles: 1,
          avgUsersPerRole: { $round: ["$avgUsersPerRole", 2] },
          maxUsersPerRole: 1,
          minUsersPerRole: 1,
        },
      },
    ]);
  },

  /**
   * 获取用户数量最多的角色
   */
  getMostPopularRoles: function (limit = 10) {
    return this.aggregate([
      {
        $group: {
          _id: "$roleId",
          userCount: { $sum: 1 },
        },
      },
      {
        $sort: { userCount: -1 },
      },
      {
        $limit: limit,
      },
      {
        $lookup: {
          from: "sys_roles",
          localField: "_id",
          foreignField: "roleId",
          as: "roleInfo",
        },
      },
      {
        $unwind: "$roleInfo",
      },
      {
        $project: {
          roleId: "$_id",
          roleName: "$roleInfo.roleName",
          roleKey: "$roleInfo.roleKey",
          userCount: 1,
          _id: 0,
        },
      },
    ]);
  },

  /**
   * 获取角色数量最多的用户
   */
  getUsersWithMostRoles: function (limit = 10) {
    return this.aggregate([
      {
        $group: {
          _id: "$userId",
          roleCount: { $sum: 1 },
        },
      },
      {
        $sort: { roleCount: -1 },
      },
      {
        $limit: limit,
      },
      {
        $lookup: {
          from: "sys_users",
          localField: "_id",
          foreignField: "userId",
          as: "userInfo",
        },
      },
      {
        $unwind: "$userInfo",
      },
      {
        $project: {
          userId: "$_id",
          userName: "$userInfo.userName",
          nickName: "$userInfo.nickName",
          roleCount: 1,
          _id: 0,
        },
      },
    ]);
  },

  /**
   * 检查用户是否拥有管理员角色
   */
  isUserAdmin: async function (userId) {
    return this.hasUserRoleByKey(userId, "ADMIN");
  },

  /**
   * 获取用户的权限字符串列表
   */
  getUserPerms: async function (userId) {
    const userRoles = await this.getUserRoles(userId);

    // 这里可以扩展为从角色关联的菜单权限中获取权限字符串
    // 目前返回角色键名作为权限标识
    return userRoles
      .filter((role) => role.status === "0")
      .map((role) => role.roleKey);
  },
};

// 查询助手
sysUserRoleSchema.query = {
  // 按用户ID筛选
  byUserId: function (userId) {
    return this.where("userId").equals(userId);
  },

  // 按角色ID筛选
  byRoleId: function (roleId) {
    return this.where("roleId").equals(roleId);
  },

  // 按创建时间范围筛选
  byCreateTimeRange: function (startDate, endDate) {
    return this.where("createTime").gte(startDate).lte(endDate);
  },
};

// 中间件
sysUserRoleSchema.pre("save", function (next) {
  // 可以在这里添加业务逻辑验证
  // 例如：验证用户和角色是否存在且状态正常

  next();
});

// 创建模型
const SysUserRole = mongoose.model("SysUserRole", sysUserRoleSchema);

module.exports = SysUserRole;
